package serviceImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DAO.BaseDAO;
import DTO.Employee;
import pageUtil.PageB;
import util.DBConnection;
import util.DBConnection.CallBack;

public class VipDAO implements BaseDAO<Employee> {

    @Override
    public boolean add(Employee t,String sql) throws Exception {
        Connection conn = DBConnection.getConn();
        boolean boo = DBConnection.executUpadte(conn, sql,
                t.getId(),
                t.getName(),
                t.getPhone(),
                t.getPassword(),
                t.getSex(),
                t.getAge(),
                t.getJob(),
                t.getCondition(),
                t.getPhoto_fwp(),
                t.getPhoto_ic(),
                t.getRegtime());

        if(conn!=null){
            conn.close();
        }
        return boo;

    }

    @Override
    public boolean delete(Employee t) throws Exception {
        String sql = "delete from employee where id=?";
        Connection conn  = DBConnection.getConn();
        boolean boo = DBConnection.executUpadte(conn, sql, t.getId());
        if(conn!=null){
            conn.close();
        }
        return boo;
    }

    @Override
    public boolean update(Employee t,String sql) throws Exception {
        Connection conn = DBConnection.getConn();
        boolean boo = DBConnection.executUpadte(conn, sql,
                t.getName(),
                t.getAge(),
                t.getSex(),
                t.getPhone(),
                t.getMessage(),
                t.getJob(),
                t.getId()
        );
        if(conn!=null){
            conn.close();
        }
        return boo;
    }

    public boolean update1(Employee t) throws Exception {
        Connection conn = DBConnection.getConn();
        String sql = "update employee set active=1,conditionn=1 WHERE id = ?";
        boolean boo = DBConnection.executUpadte(conn, sql,
                t.getId());
        if(conn!=null){
            conn.close();
        }
        return boo;
    }

    //这个方法对于员工自己修改自己的登录密码的方法
    public boolean update_psw(Employee t) throws SQLException{
        String sql = "update employee set password=? where id=?";
        Connection conn = DBConnection.getConn();
        boolean boo = DBConnection.executUpadte(conn, sql,t.getPassword(), t.getId());
        if(conn!=null){
            conn.close();

        }
        return boo;

    }


    /**
     *	员工表删除按钮第一种情况，将其状态设置为离职
     * @throws SQLException
     */
    public boolean update_condition(Employee t) throws SQLException{
        String sql = "update employee set conditionn=3 where id=?";
        Connection conn = DBConnection.getConn();
        boolean boo = DBConnection.executUpadte(conn, sql,
                t.getId());
        if(conn!=null){
            conn.close();
        }
        return boo;

    }

    /**
     * 检索全部信息
     */
    @Override
    public List<Employee> findAll(int currentPage, int pageSize,String sql) throws Exception {
//		String sql = "select id,name,password,sex,age,job,education,"
//				+ "regtime,depid,condition,specialty,active,message from employee limit?,?";
        return DBConnection.queryList(new CallBack<Employee>() {
            @Override
            public List<Employee> getDatas(ResultSet rs) {
                Employee emp = null;
                List<Employee> list = new ArrayList<Employee>();
                try {
                    while(rs.next()){
                        emp = new Employee();
                        emp.setId(rs.getString("id"));
                        emp.setName(rs.getString("name"));
                        emp.setPassword(rs.getString("password"));
                        emp.setSex(rs.getInt("sex"));
                        emp.setAge(rs.getInt("age"));
                        emp.setJob(rs.getString("job"));
                        emp.setEducation(rs.getInt("education"));
                        emp.setRegtime(rs.getString("regtime"));
                        emp.setDepid(rs.getInt("depid"));
                        emp.setCondition(rs.getInt("conditionn"));
                        emp.setSpecialty(rs.getString("specialty"));
                        emp.setActive(rs.getInt("active"));
                        emp.setMessage(rs.getString("message"));
                        emp.setPhone(rs.getString("phone"));
                        emp.setEid(rs.getString("eid"));
                        list.add(emp);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    if(rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return list;

            }


        }, sql,currentPage,pageSize);
    }

    @Override
    public List<Employee> findAll(String sql) throws Exception {
        return DBConnection.queryList(new CallBack<Employee>() {
            @Override
            public List<Employee> getDatas(ResultSet rs) {
                List<Employee> list = new ArrayList<Employee>();
                Employee emp = null;
                try {
                    while(rs.next()){
                        emp = new Employee();
                        emp.setId(rs.getString("id"));
                        emp.setName(rs.getString("name"));
                        emp.setPassword(rs.getString("password"));
                        emp.setSex(rs.getInt("sex"));
                        emp.setAge(rs.getInt("age"));
                        emp.setJob(rs.getString("job"));
                        emp.setEducation(rs.getInt("education"));
                        emp.setRegtime(rs.getString("regtime"));
                        emp.setDepid(rs.getInt("depid"));
                        emp.setCondition(rs.getInt("conditionn"));
                        emp.setSpecialty(rs.getString("specialty"));
                        emp.setActive(rs.getInt("active"));
                        emp.setMessage(rs.getString("message"));
                        emp.setPhone(rs.getString("phone"));
                        emp.setEid(rs.getString("eid"));
                        list.add(emp);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    if(rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return list;
            }


        }, sql);
    }

    @Override
    public Employee findById(String id) throws Exception {
        String sql="select id,eid,name,password,sex,age,job,education,"
                + "regtime,depid,conditionn,specialty,active,message,phone,photo_fwp,photo_ic,photo_ec from employee where id=?";
        return DBConnection.queryOne(new CallBack<Employee>() {
            @Override
            public Employee getData(ResultSet rs) {
                Employee emp = null;
                try {
                    if(rs.next()){
                        emp = new Employee();
                        emp.setId(rs.getString("id"));
                        emp.setName(rs.getString("name"));
                        emp.setPassword(rs.getString("password"));
                        emp.setSex(rs.getInt("sex"));
                        emp.setAge(rs.getInt("age"));
                        emp.setJob(rs.getString("job"));
                        emp.setEducation(rs.getInt("education"));
                        emp.setRegtime(rs.getString("regtime"));
                        emp.setDepid(rs.getInt("depid"));
                        emp.setCondition(rs.getInt("conditionn"));
                        emp.setSpecialty(rs.getString("specialty"));
                        emp.setActive(rs.getInt("active"));
                        emp.setMessage(rs.getString("message"));
                        emp.setPhone(rs.getString("phone"));
                        emp.setPhoto_fwp(rs.getString("photo_fwp"));
                        emp.setPhoto_ic(rs.getString("photo_ic"));
                        emp.setPhoto_ec(rs.getString("photo_ec"));
                        emp.setEid(rs.getString("eid"));

                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    if(rs!= null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return emp;
            }


        }, sql, id);
    }

    @Override
    public Employee findByPropert(Employee t) throws Exception {
        String sql = "select id,eid,name,password,sex,age,job,education,"
                + "regtime,depid,conditionn,specialty,active,message,phone,photo_fwp,photo_ic,photo_ec from employee where name=?";
        return DBConnection.queryOne(new CallBack<Employee>() {
            @Override
            public Employee getData(ResultSet rs) {
                Employee emp = null;
                try {
                    if(rs.next()){
                        emp = new Employee();
                        emp.setId(rs.getString("id"));
                        emp.setName(rs.getString("name"));
                        emp.setPassword(rs.getString("password"));
                        emp.setSex(rs.getInt("sex"));
                        emp.setAge(rs.getInt("age"));
                        emp.setJob(rs.getString("job"));
                        emp.setEducation(rs.getInt("education"));
                        emp.setRegtime(rs.getString("regtime"));
                        emp.setDepid(rs.getInt("depid"));
                        emp.setCondition(rs.getInt("conditionn"));
                        emp.setSpecialty(rs.getString("specialty"));
                        emp.setActive(rs.getInt("active"));
                        emp.setMessage(rs.getString("message"));
                        emp.setPhone(rs.getString("phone"));
                        emp.setPhoto_fwp(rs.getString("photo_fwp"));
                        emp.setPhoto_ic(rs.getString("photo_ic"));
                        emp.setPhoto_ec(rs.getString("photo_ec"));
                        emp.setEid(rs.getString("eid"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    if(rs!= null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return emp;
            }


        }, sql, t.getName());
    }

    @Override
    public int dataCount(String sql) throws Exception {

        return DBConnection.queryOne(new CallBack<Integer>() {
            @Override
            public Integer getData(ResultSet rs) {
                int count = 0;
                try {
                    if(rs.next()){
                        count = rs.getInt(1);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally{
                    if(rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                return count;

            }
        }, sql);
    }

    @Override
    public List<Employee> findByPage(PageB<Employee> bean,String sql) throws SQLException {
//		sql = "select id,name,password,sex,age,job,education,"
//				+ "regtime,depid,conditionn,specialty,active,message,phone from employee where active=1 and conditionn in(1,2) limit ?,?";
        int start = (bean.getCurrentPage()-1)*bean.getPageSize();
        return DBConnection.queryList(new CallBack<Employee>() {

            @Override
            public List<Employee> getDatas(ResultSet rs) {
                List<Employee> list = new  ArrayList<Employee>();
                Employee emp = null;
                try {
                    while(rs.next()){
                        emp = new Employee();
                        emp.setId(rs.getString("id"));
                        emp.setName(rs.getString("name"));
                        emp.setPassword(rs.getString("password"));
                        emp.setSex(rs.getInt("sex"));
                        emp.setAge(rs.getInt("age"));
                        emp.setJob(rs.getString("job"));
                        emp.setEducation(rs.getInt("education"));
                        emp.setRegtime(rs.getString("regtime"));
                        emp.setDepid(rs.getInt("depid"));
                        emp.setCondition(rs.getInt("conditionn"));
                        emp.setSpecialty(rs.getString("specialty"));
                        emp.setActive(rs.getInt("active"));
                        emp.setMessage(rs.getString("message"));
                        emp.setPhone(rs.getString("phone"));
                        emp.setEid(rs.getString("eid"));
                        list.add(emp);
                    }
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }finally{
                    if(rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }

                return list;
            }

        }, sql, start,bean.getPageSize());
    }


}
